﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Utility;
using Portal.Exam.Model;
using Portal.Core;
using Portal.Web.Utility;

namespace  Portal.Exam.Bussiness
{
    public class MonthiBll
    {
        static string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString = WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }

        public MonthiCls CreateModel(string SiteId, string MonthiId)
        {
            string Query = " select TableMonthi.* from TableMonthi where MonthiId = '" + MonthiId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;

            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);

            MonthiCls tMonthiCls = new MonthiCls();
            tMonthiCls.MonthiId = (string)dtResult.Rows[0]["MonthiId"];
            tMonthiCls.MonthiName = (string)dtResult.Rows[0]["Name"];
            string GroupQuestionId=(string)dtResult.Rows[0]["fk_GroupQuestion_Id"];
            tMonthiCls.GroupQuestionCls = GroupQuestionBll.CreateGroupQuestionModel(SiteId, GroupQuestionId);

            return tMonthiCls;
        }



        public DataTable ReadingMonthi(string SiteId, string GroupQuestionId, string Keyword)
        {
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select MonthiId,[Name],Question=(select count(QuestionId) from TableQuestion where fk_Monthi_Id=MonthiId) from TableMonthi where fk_GroupQuestion_Id='"+GroupQuestionId+"'";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and [Name] like N'%" + Keyword + "%'";
            }
            Query += " order by [Name]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }


        public DataTable ReadingMonthiForLargeFreeText(
            string SiteId, 
            string ExamCode,
            string GroupQuestionId)
        {
            DataTable dataTable = new DataTable();
            string Query = " select MonthiId,[Name] from TableMonthi where fk_GroupQuestion_Id='" + GroupQuestionId + "'"+
                           "    and MonthiId in (select fk_Monthi_Id from TableQuestion where [Type]='LargeFreeText' and QuestionId not in (select ExamQuestionId from TableExamQuestion where ExamCode='"+ExamCode+"'))"+
                           " order by [Name]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public string CreateMonthi(
            SqlTransaction _SqlTransaction,
            string SiteId, 
            string MonthiName,
            string GroupQuestionId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewMonthiId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableMonthi(MonthiId,Name,fk_GroupQuestion_Id, AssetCode) values (@MonthiId,@Name,@fk_GroupQuestion_Id, @AssetCode)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@MonthiId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@MonthiId"].Value = NewMonthiId;

            TempSqlCommand.Parameters.Add("@Name", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Name"].Value = MonthiName;

            TempSqlCommand.Parameters.Add("@fk_GroupQuestion_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_GroupQuestion_Id"].Value = GroupQuestionId;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = WebSessionUtility.GetCurrentLoginUser().AssetCode;

            

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewMonthiId;
        }



        public void UpdateMonthi(
            SqlTransaction _SqlTransaction,
            string SiteId, 
            string MonthiId,
            string MonthiName)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableMonthi set Name=@Name where MonthiId=@MonthiId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@MonthiId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@MonthiId"].Value = MonthiId;

            TempSqlCommand.Parameters.Add("@Name", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Name"].Value = MonthiName;

            
            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void DeleteMonthi(
            SqlTransaction _SqlTransaction,
            string SiteId, 
            string MonthiId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query =
                " Delete TableAnswer where fk_Question_Id in (select QuestionId from TableQuestion where fk_Monthi_Id=@MonthiId); " +
                " Delete TableQuestion where fk_Monthi_Id=@MonthiId; "+
                " Delete TableExamTemplateConfig where fk_Monthi_Id=@MonthiId; " +
                " Delete TableMonthi where MonthiId=@MonthiId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@MonthiId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@MonthiId"].Value = MonthiId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }
    }
}
